Prod_Rank_List =[]
Level_Id_List =[]
Date=""
rank=0
SetDateFlag = False
itertn=0
conn = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};SERVER=PRANAY-PC\SQLSERVER2014;DATABASE=FKWMS;UID=sa;PWD=SQL@2014')
conn.autocommit= True
cursor = conn.cursor()
def Level_Rank_Input():
global level_rank
print "Products in Warehouse are ranked between 1 to 11 as per their probablity of being ordered \n"
print "Higher Demand of Product Item would imply Lower Ranking of the Item \n"
print "Rank 0 corresponds to no product... It implies that slot is empty bin \n"
level_rank = input("Enter the Level Rank for which Heat Map is to be Displayed : ")
if(level_rank>6 or level_rank< 1 ):
print "Leval Rank value should be between 1 and 6 "
Level_Rank_Input()
return level_rank
def OptionRearrangeBins():
global option
option = input("Please Enter Option 1 or 2 : ")
if(option>2 or option< 1 ):
print "Option value should be 1 or 2 "
OptionRearrangeBins()
return option
def SP_HeatMap(option):
global cursor,itertn,conn
itertn+=1
conn.autocommit= True
NorecsUpdated = 0
if(option==1):
if(itertn>1):
print "Rearranging by Total Number of Items Ordered : " + "\n"
cursor.execute("exec [dbo].[USP_HeatMap_for_Quantity_V1_1] @order_date= '"+Date+"'")
cursor.commit()
NorecsUpdated = cursor.execute("exec [dbo].[USP_HeatMap_Update_Test]").rowcount
cursor.commit()
print "NorecsUpdated : "+str(NorecsUpdated)+ "\n"
if(NorecsUpdated>0):
HeatMap_RankMatrix_perLevel(level_rank)
SP_HeatMap(option)
elif(NorecsUpdated<=0):
print "Already Updated....Further Rearrangement of Bins not Required for Level "+ str(level_rank) +" for Order Date : "+Date+ "\n"
elif(option==2):
if(itertn>1):
print "Rearranging by Number of times an Order has been placed for an Item : " + "\n"
cursor.execute("exec [dbo].[USP_HeatMap_for_Frequency_V1_1] @Order_Date= '"+Date+"'")
cursor.commit()
NorecsUpdated = cursor.execute("exec [dbo].[USP_HeatMap_Update_Test]").rowcount
cursor.commit()
print "NorecsUpdated : "+str(NorecsUpdated)+ "\n"
if(NorecsUpdated>0):
HeatMap_RankMatrix_perLevel(level_rank)
SP_HeatMap(option)
elif(NorecsUpdated<=0):
print "Already Updated....Further Rearrangement of Bins not Required for Level "+ str(level_rank) +" for Order Date : "+Date+ "\n"
def RearrangeBinsLogic(option,level_rank):
global cursor,conn,Date,SetDateFlag
if option==1:
print "Rearranging by Total Number of Items Ordered : " + "\n"
if(SetDateFlag is False):
year =input("Enter Year \t")
month =input("Enter Month \t")
day =input("Enter Date \t")
Date = str(year)+"-"+str(month)+"-"+str(day)
SetDateFlag = True
SP_HeatMap(option)
elif option==2:
print "Rearranging by Number of times an Order has been placed for an Item : " + "\n"
if(SetDateFlag is False):
year =input("Enter Year \t")
month =input("Enter Month \t")
day =input("Enter Date \t")
Date = str(year)+"-"+str(month)+"-"+str(day)
SetDateFlag = True
SP_HeatMap(option)
def HeatMap_RankMatrix_perLevel(level_rank):
global cursor,Prod_Rank_List,Level_Id_list
Prod_Rank_List =[]
Level_Id_List = []
cursor.execute("exec [dbo].[USP_HeatMap_RankMatrix_perLevel] @Level_Rank= '"+str(level_rank)+"'")
rowsp = cursor.fetchone()
while rowsp:
if(rowsp[0] is not None):
Prod_Rank_List.append(int(rowsp[0]))
Level_Id_List.append(int(rowsp[1]))
elif(rowsp[0] is None):
Prod_Rank_List.append(0)
Level_Id_List.append(int(rowsp[1]))
rowsp = cursor.fetchone()
cursor.commit()
HeatMap_Display(Prod_Rank_List,Level_Id_List)
def HeatMap_Display(Prod_Rank_List,Level_Id_List) :
WareHouse_LevelId_Array = np.array(Level_Id_List)
WareHouse_Rank_Array = np.array(Prod_Rank_List)
levelId_t =WareHouse_LevelId_Array.reshape(2320,1)
ware_t = WareHouse_Rank_Array.reshape(2320,1)
# ware_t = WareHouse_Rank_Array.reshape(80,1)
Rank_Matrix = ware_t.reshape(58,40)
# Rank_Matrix = ware_t.reshape(2,40)
LevelID_Matrix =levelId_t.reshape(58,40)
Rank_Matrix_Trnspsose = Rank_Matrix.T
Rank_Matrix_Trnspsose_Reverse = Rank_Matrix_Trnspsose[::-1]
LevelID_Matrix_Transpose = LevelID_Matrix.T
LevelID_Matrix_Transpose_Reverse = LevelID_Matrix_Transpose[::-1]
x=['C1','C2','C3','C4','C5','C6','C7','C8','C9','C10','C11','C12','C13','C14','C15','C16','C17','C18','C19','C20','C21','C22','C23','C24','C25','C26','C27','C28','C29','C30','C31','C32','C33','C34','C35','C36','C37','C38','C39','C40','C41','C42','C43','C44','C45','C46','C47','C48','C49','C50','C51','C52','C53','C54','C55','C56','C57','C58']
y=['B1','B2','B3','B4','B5','B6','B7','B8','B9','B10','B11','B12','B13','B14','B15','B16','B17','B18','B19','B20','B21','B22','B23','B24','B25','B26','B27','B28','B29','B30','B31','B32','B33','B34','B35','B36','B37','B38','B39','B40']
%matplotlib inline
plotly.offline.init_notebook_mode()
annotations = go.Annotations()
hovertext = list()
for n,row in enumerate(Rank_Matrix_Trnspsose):
hovertext.append(list())
for m, val in enumerate(row):
annotations.append(go.Annotation(text=str(LevelID_Matrix_Transpose[n][m]), x=x[m], y=y[n],
xref='x1', yref='y1', showarrow=False))
if(Rank_Matrix_Trnspsose[n][m] != 0):
hovertext[-1].append('Column: {}<br />Band: {}<br />Prod Rank: {}<br />Level ID: {}'.format(x[m], y[n], Rank_Matrix_Trnspsose[n][m],str(LevelID_Matrix_Transpose[n][m])))
elif(Rank_Matrix_Trnspsose[n][m] == 0):
hovertext[-1].append('Column: {}<br />Band: {}<br />{}<br />Level ID: {}'.format(x[m], y[n],'Empty Level',str(LevelID_Matrix_Transpose[n][m])))
height, width = Rank_Matrix_Trnspsose.shape
# trace = go.Heatmap(x=x, y=y, z=Rank_Matrix_Trnspsose, colorscale='Viridis', showscale=True,hoverinfo='text',text=hovertext)
trace = go.Heatmap(x=x, y=y, z=Rank_Matrix_Trnspsose, showscale=True,hoverinfo='text',text=hovertext)
fig = go.Figure(data=go.Data([trace]))
fig['layout'].update(
title="Annotated Heatmap",
annotations=annotations,
xaxis=go.XAxis(ticks='', side='top'),
width=2500,
height=1000,
autosize=False
)
plotly.offline.iplot(fig, filename='annotated_heatmap_text')
Prod_Rank_List =[]
Level_Id_List =[]
class PythonDbConnect:
def __init__(self, name):
self.name = name
def PrintRecordsFromDatabase(self):
global level_rank,Prod_Rank_List,option,cursor,conn,rank
# conn = pymssql.connect(host="PRANAY-PC\SQLSERVER2014", user='sa', password='SQL@2014', database='FKWMS')
print "HEAT MAP MATRIX : " +"\n"
level_rank = Level_Rank_Input()
print "level Rank is : ", level_rank
HeatMap_RankMatrix_perLevel(level_rank)
print "How do you want to Rearrange the Bins in the WareHouse Shelves ? " + "\n"
print "Based on 1) Total number of Items Ordered " + "\n"
print " 2) Number of times an Order has been placed for an Item "+ "\n"
option = OptionRearrangeBins()
RearrangeBinsLogic(option,level_rank)
if __name__ == "__main__":
global SetDateFlag
SetDateFlag = False
dbObj = PythonDbConnect("Connect MS SQL")
dbObj.PrintRecordsFromDatabase()